import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
# 这里从mlxtend工具包中导入散点图矩阵工具
from mlxtend.plotting import scatterplotmatrix
from mlxtend.plotting import heatmap

bf_no = 1
df_coef = pd.read_excel('铁水硫模型参数.xlsx')
df_coef.columns = df_coef.columns.str.upper()
gamma = df_coef.loc[0]['GAMMA']
neighbors_num = df_coef.loc[0]['NEIGHBORS_NUM']
guiyihua = df_coef.loc[0]['GUIYIHUA']
modelname = df_coef.loc[0]['MODELNAME']
# gamma = 1.5
# neighbors_num = 20
# guiyihua = 'MinMaxScaler'
# modelname = 'ExtraTrees'
#读取该BF的历史数据
#SQL查询，本地暂时使用文件
xlsx_name = 'D:/repos/sicost/fe_s_' + str(bf_no) +'.xlsx'
df0 = pd.read_excel(xlsx_name)
df0.columns = df0.columns.str.upper()
df0['PROD_DATE'] = df0['PROD_DATE'].astype(str)
df0_train = df0.copy()
df0_train = df0_train.reset_index(drop=True)
df0_train.drop(['UNIT_NO'], axis=1, inplace=True)
#数据清理
def clean_data(df, gamma):
    column_name_list = df.columns.tolist()
    column_name_list.remove('PROD_DATE')
    column_name_num = len(column_name_list)
    clean_str_start = 'df_new = df['
    clean_str_end = ']'
    ldict1 = {}
    for i in range(0, column_name_num):
        print(i)
        print(column_name_list[i])
        column_name_tmp = column_name_list[i]
        exec("q1_{} = df['{}'].quantile(0.25)".format(i, column_name_tmp), locals(), ldict1)
        exec("q3_{} = df['{}'].quantile(0.75)".format(i, column_name_tmp), locals(), ldict1)
        exec("iqr_val_{} = q3_{} - q1_{}".format(i, i, i), locals(), ldict1)
        exec('''clean_str1 = "(df['{}'] >= ldict1['q1_{}'] - gamma * ldict1['iqr_val_{}'])"'''.format(column_name_tmp, i, i), locals(), ldict1)
        exec('''clean_str2 = "(df['{}'] < ldict1['q3_{}'] + gamma * ldict1['iqr_val_{}'])"'''.format(column_name_tmp, i, i), locals(), ldict1)
        exec('''clean_str3 = "(df['{}'] > 0)"'''.format(column_name_tmp), locals(), ldict1)
        clean_str1 = ldict1["clean_str1"]
        clean_str2 = ldict1["clean_str2"]
        clean_str3 = ldict1["clean_str3"]
        if i == 0:
            clean_str_start = clean_str_start + clean_str1 + ' & ' + clean_str2 + ' & ' + clean_str3
        else:
            clean_str_start = clean_str_start + ' & ' + clean_str1 + ' & ' + clean_str2 + ' & ' + clean_str3
    clean_str = clean_str_start + clean_str_end
    print(clean_str)
    exec(clean_str, locals(), ldict1)
    df_new = ldict1["df_new"]
    df_new = df_new.reset_index(drop=True)
    return df_new

df0_train_clean = clean_data(df0_train, gamma)
df0_train_clean.drop(['PROD_DATE'], axis=1, inplace=True)
df0_train_clean.drop(['SUM_CACULATE_IRON_WGT'], axis=1, inplace=True)




cols = ['AVG_IRON_TEMP', 'AVG_C_S_VALUE', 'COMPUTE_SLAG_RATE', 'COMPUTE_FILL_S_VALUE', 'AVG_S_VALUE']

scatterplotmatrix(df0_train_clean[cols].values, figsize=(10, 8),
                  names=cols, alpha=0.5)
plt.tight_layout()
plt.savefig('0509_01.png', dpi=300)
plt.show()



cm = np.corrcoef(df0_train_clean[cols].values.T)
hm = heatmap(cm, row_names=cols, column_names=cols, figsize=(15,15))

plt.savefig('0509_02.png', dpi=300)
plt.show()
print('finish')